﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class TipReportController
    {
        DBConnector connector;
        public TipReportController()
        {
            connector = new DBConnector();
        }

        private double getTotalDailyTip()
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            double genTotal = 500;
            DateTime startDate = DateTime.Parse(miscChargeControl.getCurrentDate());
            DateTime endDate = DateTime.Parse(miscChargeControl.getCurrentEnd());
            string query = "SELECT SUM(tipOftheDay) from tip WHERE dateTip >= \'" + startDate.ToString("yyyy-MM-dd") + "\' && dateTip <= \'" + endDate.ToString("yyyy-MM-dd") + "\'";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    String retrieved = dataReader[0] + "";
                    if (retrieved.Equals(""))
                    {
                        genTotal = 0;
                    }
                    else
                        genTotal = (double)dataReader[0];
                }
                dataReader.Close();
                connector.closeConnection();
            }
     //       else
    //        {
    //            MessageBox.Show(startDate.ToString("yyyy-MM-dd") + " " + endDate.ToString("yyyy-MM-dd"));
     //           MessageBox.Show("total daily tip: " + genTotal);
    //        }
            return genTotal;
        }



        private double getCharges(int employeeCode)
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            double charges = 0;
            string query = "SELECT SUM(chargedAmount) from tipcharges where periodID = " + miscChargeControl.getPeriodID() + " and employeeCode = " + employeeCode;
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    String retrieved = dataReader[0] + "";
                    if (retrieved.Equals(""))
                    {
                        charges = 0;
                    }
                    else
                        charges = Double.Parse(dataReader[0] + "");
                }
                dataReader.Close();
                connector.closeConnection();
            }
       //     MessageBox.Show("Charges: " + charges);
            return charges;
        }

        private double getPreviousTipCharges()
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            double previousTipCharges = 0;
            DateTime endDate = DateTime.Parse(miscChargeControl.getCurrentDate());
            endDate.AddDays(-1);
            string query = "SELECT sum(chargedAmount) from tipcharges where periodID = (SELECT periodID from period where periodEnd = \'" + endDate.ToString("yyyy-MM-dd") + "\')";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    String retrieved = dataReader[0] + "";
                    if (retrieved.Equals(""))
                    {
                        previousTipCharges = 0;
                    }
                    else
                        previousTipCharges = (double)dataReader[0];
                }
                dataReader.Close();
                connector.closeConnection();
            }
        //    MessageBox.Show("Previous tip charges: " + previousTipCharges);
            return previousTipCharges;
        }

        private double getPrevious75Deduction()
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            double previous75Deductions = 0;
            DateTime endDate = DateTime.Parse(miscChargeControl.getCurrentDate());
            endDate.AddDays(-1);
            string query = "SELECT sum(totalTip) from tipreport where deduction = 1 and periodID = (SELECT periodID from period where periodEnd = \'" + endDate.ToString("yyyy-MM-dd") + "\');";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    String retrieved = dataReader[0] + "";
                    if (retrieved.Equals(""))
                    {
                        previous75Deductions = 0;
                    }
                    else
                        previous75Deductions = Double.Parse(dataReader[0] + "");
                }
                dataReader.Close();
                connector.closeConnection();
            }
        //    MessageBox.Show("Previous 75 deduction: " + previous75Deductions * 3);
            return previous75Deductions * 3;
        }

        private double getGeneralTotal()
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            double generalTotal = getTotalDailyTip() + getPreviousTipCharges() + getPrevious75Deduction() - (double)miscChargeControl.getCurrentChargeAmount();
   //         MessageBox.Show("General total: " + generalTotal);
            return generalTotal;
        }

        private double getGeneralTip()
        {
            int count = new EmployeeController().countEmployees();
            double generalTip = (getGeneralTotal() * 0.89) / (double)count;
   //         MessageBox.Show("General Tip: " + generalTip);
            return generalTip;
        }

        private double getAbsence(int employeeCode)
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            int absences = 0;
            DateTime periodStart = DateTime.Parse(miscChargeControl.getCurrentDate());
            DateTime periodEnd = DateTime.Parse(miscChargeControl.getCurrentEnd());
            TimeSpan diff = periodEnd.Subtract(periodStart);
            int days = diff.Days + 1;
            string query = "SELECT absences from attendance where periodId = " + miscChargeControl.getPeriodID() + " and employeeCode = " + employeeCode;
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    absences = (int)dataReader[0];
                dataReader.Close();
                connector.closeConnection();
            }
            double absenceCharge = (double)absences / (double)days * getGeneralTip();
     //       MessageBox.Show("Absence charge: " + absenceCharge);
            return absenceCharge;
        }

        private double getSubTotal(int employeeCode)
        {
            double subTotal = getGeneralTip() - getCharges(employeeCode) - getAbsence(employeeCode);
     //       MessageBox.Show("Subtotal: " + subTotal);
            return subTotal;
        }

        private double getSumTipReportAbsences()
        {
            double sum = 0;
            EmployeeController empC = new EmployeeController();
            List<int> list = empC.getAllEmployeeCode();
            foreach (int code in list)
            {
                sum = sum + getAbsence(code);
            }
       //     MessageBox.Show("Sum of Tip report absences: " + sum);
            return sum;
        }

        private double getTotalTip(int employeeCode)
        {
            double totalTip = 0;
            EmployeeController employeeControl = new EmployeeController();
            int count = employeeControl.countEmployees();
            totalTip = getSubTotal(employeeCode) + (getSumTipReportAbsences() / (double)count);
            if (employeeControl.getPosition(employeeCode) == 1)
            {
                totalTip = totalTip + (getGeneralTotal() * 0.1 / (double)employeeControl.countSuperCaptain());
                totalTip = totalTip + (getGeneralTotal() * 0.1 / (double)employeeControl.countSuperGuard());
            }
            else if (employeeControl.getPosition(employeeCode) == 2)
            {
                totalTip = totalTip + (getGeneralTotal() * 0.1 / (double)employeeControl.countSuperCaptain());
            }
            else if (employeeControl.getPosition(employeeCode) == 3)
            {
                totalTip = totalTip + (getGeneralTotal() * 0.1 / (double)employeeControl.countSuperGuard());
            }
            if (hasDeduction(employeeCode))
            {
                totalTip = totalTip * 0.25;
            }
        //    MessageBox.Show("Total tip: " + totalTip);
            return totalTip;
        }

        private bool hasDeduction(int employeeCode)
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            int deduction = 0;
            string query = "SELECT deduction from tipreport where employeeCode = " + employeeCode + " and periodID = " + miscChargeControl.getPeriodID();
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    deduction = (int)dataReader["deduction"];
                }
                dataReader.Close();
                connector.closeConnection();
            }

            if (deduction == 0)
                return false;
            else
                return true;
        }

        public void addTipReport(TipReport tipreport)
        {
            MiscChargeController miscChargeControl = new MiscChargeController();
            string query = "SELECT * FROM tipreport where periodID = " + miscChargeControl.getPeriodID() + " and employeeCode = " + tipreport.getEmployeeCode();
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                if (dataTable.Rows.Count > 0)
                {
                    query = "UPDATE tipreport set generalTip =  " + getGeneralTip() + ", charges = " + getCharges(tipreport.getEmployeeCode()) + ", absences = " + getAbsence(tipreport.getEmployeeCode()) + ", subtotal = " + getSubTotal(tipreport.getEmployeeCode()) + ", totalTip = " + getTotalTip(tipreport.getEmployeeCode()) + " WHERE employeeCode = " + tipreport.getEmployeeCode() + " AND periodID = " + miscChargeControl.getPeriodID();
                    if (connector.openConnection())
                    {
                        try
                        {
                            MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
                        }
                        connector.closeConnection();
                    }
                }
                else
                {
                    query = "INSERT INTO tipreport(employeeCode, periodID, generalTip, charges, absences, subtotal, deduction, totalTip) VALUES(" + tipreport.getEmployeeCode() + ", " + miscChargeControl.getPeriodID() + ", " + getGeneralTip() + ", " + getCharges(tipreport.getEmployeeCode()) +
                        "," + getAbsence(tipreport.getEmployeeCode()) + "," + getSubTotal(tipreport.getEmployeeCode()) + ", 0, " + getTotalTip(tipreport.getEmployeeCode()) + ");";
                    if (connector.openConnection())
                    {
                        try
                        {
                            MySqlCommand cmd2 = new MySqlCommand(query, connector.getConnection());
                            cmd2.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            //MessageBox.Show("Cannot initialize employee tip report", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
                        }
                        connector.closeConnection();
                    }
                }
            }
        }

    }
}